﻿
Public Class frmCompany
    Dim dsComp As New DataSet
    Dim daComp As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub frmCompany_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        e.Cancel = True
        Me.Hide()
    End Sub

    Private Sub frmCompany_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call gSubIconShow(Me)
        Call gSubMkCmbMemFlag(cmbFlag)
        Call gridRefresh("default")
    End Sub


    Private Sub cmbFlag_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbFlag.KeyPress
        e.Handled = True
    End Sub

    Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
        Call txtReset()
    End Sub

    Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
        Call gridRefresh("")

    End Sub

    Private Sub btnSearchReset_Click(sender As System.Object, e As System.EventArgs) Handles btnSearchReset.Click
        txtSearch.Clear()
        Call gridRefresh("")
    End Sub

    Private Sub txtSearch_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtSearch.KeyPress
        If Asc(e.KeyChar) = 13 Then
            Call gridRefresh("")
        End If
    End Sub

#Region "프로시져"

    Private Sub gridRefresh(ByVal initFlag As String)
        Call dbConnection()
        Dim addSql As String = ""

        dsComp = New DataSet
        If txtSearch.Text <> "" Then
            addSql = " AND company LIKE '%" + txtSearch.Text + "%' "
        End If
        If initFlag = "default" Then
            addSql = " AND compCd=0 "
        End If
        sql = "SELECT compCd, company, compNo, owner, phone, fax, major, cellPhone, addr, types, article, IIF(flag='H','정지','유효') FROM tblCompany WHERE flag<>'N' " + addSql + " ORDER BY flag DESC, company "
        'MsgBox(sql)

        Try
            daComp = New OleDb.OleDbDataAdapter(sql, dbConn)
            daComp.Fill(dsComp, "Company")

            grdCompany.DataSource = dsComp.Tables("Company")
            lbGridCnt.Text = "총 " + dsComp.Tables("Company").Rows.Count.ToString + " 건"

            dbConn.Close()
            Call setGridDesign()

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setGridDesign()

        grdCompany.Columns(0).Visible = False
        grdCompany.Columns(1).HeaderText = "회사명"
        grdCompany.Columns(1).Width = "200"
        grdCompany.Columns(2).HeaderText = "사업자번호"
        grdCompany.Columns(3).HeaderText = "대표자"
        grdCompany.Columns(3).Width = "70"
        grdCompany.Columns(4).HeaderText = "연락처"
        grdCompany.Columns(5).HeaderText = "FAX"
        grdCompany.Columns(6).HeaderText = "담당자"
        grdCompany.Columns(7).HeaderText = "휴대전화"
        grdCompany.Columns(8).Width = "200"
        grdCompany.Columns(8).HeaderText = "주소"
        grdCompany.Columns(9).Width = "80"
        grdCompany.Columns(9).HeaderText = "업태"
        grdCompany.Columns(10).Width = "80"
        grdCompany.Columns(10).HeaderText = "종목"
        grdCompany.Columns(11).Width = "70"
        grdCompany.Columns(11).HeaderText = "유효"

        With grdCompany.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleCenter

        End With
        grdCompany.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        Call setHolding()
    End Sub

    Private Sub setHolding()
        For i = 0 To grdCompany.Rows.Count - 1
            If IsNothing(grdCompany.Rows(i).Cells(7).Value) = False Then
                If grdCompany.Rows(i).Cells(7).Value = "정지" Then
                    grdCompany.Rows(i).DefaultCellStyle.BackColor = Color.LightGray
                End If
            End If
        Next
    End Sub

    Private Sub grdCompany_CellClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdCompany.CellClick
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If IsDBNull(grdCompany.Rows(e.RowIndex).Cells(0).Value) = True Then
            Exit Sub
        End If
        sql = "SELECT compCd, company, compNo, owner, phone, major, cellPhone, fax, addr, memo, flag, types, article  FROM tblCompany WHERE compCd=" & grdCompany.Rows(e.RowIndex).Cells(0).Value

        Call dbConnection()
        dsComp = New DataSet
        daComp = New OleDb.OleDbDataAdapter(sql, dbConn)
        daComp.Fill(dsComp, "companyInfo")

        dbConn.Close()

        txtReset()

        txtCompCd.Text = dsComp.Tables("companyInfo").Rows(0).Item(0)
        txtCompany.Text = dsComp.Tables("companyInfo").Rows(0).Item(1)
        txtCompNo.Text = dsComp.Tables("companyInfo").Rows(0).Item(2)
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(3)) = False Then
            txtOwner.Text = dsComp.Tables("companyInfo").Rows(0).Item(3)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(4)) = False Then
            txtPhone.Text = dsComp.Tables("companyInfo").Rows(0).Item(4)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(5)) = False Then
            txtMajor.Text = dsComp.Tables("companyInfo").Rows(0).Item(5)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(6)) = False Then
            txtCellPhone.Text = dsComp.Tables("companyInfo").Rows(0).Item(6)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(7)) = False Then
            txtFax.Text = dsComp.Tables("companyInfo").Rows(0).Item(7)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(8)) = False Then
            txtAddr.Text = dsComp.Tables("companyInfo").Rows(0).Item(8)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(9)) = False Then
            txtMemo.Text = dsComp.Tables("companyInfo").Rows(0).Item(9)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(10)) = False Then
            cmbFlag.SelectedValue = dsComp.Tables("companyInfo").Rows(0).Item(10)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(11)) = False Then
            txtTypes.Text = dsComp.Tables("companyInfo").Rows(0).Item(11)
        End If
        If IsDBNull(dsComp.Tables("companyInfo").Rows(0).Item(12)) = False Then
            txtArticle.Text = dsComp.Tables("companyInfo").Rows(0).Item(12)
        End If
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click

        Call dbConnection()
        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        Dim rtnMsg As String
        Dim valCompany, valCompNo, valOwner, valPhone, valMajor, valCellPhone, valFax, valAddr, valMemo, valFlag, valTypes, valArticle, valCompCd As String
        Dim curDt As DateTime = Now

        valCellPhone = gFnChkVal(txtCellPhone.Text)
        valCompany = gFnChkVal(txtCompany.Text)
        valOwner = gFnChkVal(txtOwner.Text)
        valCompNo = gFnChkVal(txtCompNo.Text)
        valPhone = gFnChkVal(txtPhone.Text)
        valMajor = gFnChkVal(txtMajor.Text)
        valMemo = gFnChkVal(txtMemo.Text)
        valAddr = gFnChkVal(txtAddr.Text)
        valFax = gFnChkVal(txtFax.Text)
        valTypes = gFnChkVal(txtTypes.Text)
        valArticle = gFnChkVal(txtArticle.Text)
        valCompCd = txtCompCd.Text
        valFlag = cmbFlag.SelectedValue

        If valCompany = "" Then
            MsgBox("회사명을 바르게 입력해 주세요")
            txtCompany.Focus()
            dbConn.Close()
            Exit Sub
        End If


        If txtCompCd.Text = "" Then
            sql = "INSERT INTO tblCompany(company, compNo, owner, phone, major, cellPhone, fax, addr, [memo], types, article, flag, addDt) VALUES('" + valCompany + "','" + valCompNo + "','" + valOwner + "','" + valPhone + "','" + valMajor + "','" + valCellPhone + "','" + valFax + "','" + valAddr + "','" + valMemo + "','" + valTypes + "','" + valarticle + "','" + valFlag + "','" + curDt + "')"
            rtnMsg = "거래처 정보가 등록되었습니다"
        Else
            sql = "UPDATE tblCompany SET company='" + valCompany + "', compNo='" + valCompNo + "', owner='" + valOwner + "', phone='" + valPhone + "', major='" + valMajor + "', cellPhone='" + valCellPhone + "', fax='" + valFax + "', addr='" + valAddr + "', [memo]='" + valMemo + "', types='" + valTypes + "', article='" + valArticle + "', flag='" + valFlag + "', updateDt='" + curDt + "' WHERE compCd=" + txtCompCd.Text
            rtnMsg = "거래처 정보가 수정되었습니다"
        End If

        oleCmd = New OleDb.OleDbCommand(sql, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        Try

            oleCmd.ExecuteNonQuery()
            oleTran.Commit()

            dbConn.Close()
            MsgBox(rtnMsg)
            Call gridRefresh("")
            Call txtReset()
        Catch err As OleDb.OleDbException
            oleTran.Rollback()
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub txtReset()
        txtCompany.Clear()
        txtCompNo.Clear()
        txtMajor.Clear()
        txtOwner.Clear()
        txtPhone.Clear()
        txtCellPhone.Clear()
        txtFax.Clear()
        txtMemo.Clear()
        txtCompCd.Clear()
        txtAddr.Clear()
        txtArticle.Clear()
        txtTypes.Clear()
        cmbFlag.SelectedIndex = 0

    End Sub
#End Region

    Private Sub btn_xlsDown_Click(sender As System.Object, e As System.EventArgs) Handles btn_xlsDown.Click
        If FolderBrowserDlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
            lbTarget.Text = FolderBrowserDlg.SelectedPath + "/거래처.xlsx"
        Else
            Exit Sub
        End If

        If grdCompany.RowCount = 0 Then
            MsgBox("먼저 거래처를 조회해 주세요")
            lbTarget.Text = ""
            Exit Sub
        End If

        gGrid2Excel(grdCompany, lbTarget.Text, btn_xlsDown, prgressBar, 1)

    End Sub

    Private Sub lbTarget_Click(sender As System.Object, e As System.EventArgs) Handles lbTarget.Click
        If lbTarget.Text <> "" Then
            Process.Start(lbTarget.Text)
        End If
    End Sub

    Private Sub btnXlsUp_Click(sender As System.Object, e As System.EventArgs) Handles btnXlsUp.Click
        frmXLSCompanyUp.Show()
    End Sub
End Class
